﻿using System.Data;
using System.Data.SqlClient;
using consultasSql.consultasVarias;

namespace ConsultasDeDeguridad
{
    public class CCPerfilesDeUsuario : CDarDataSet
    {
        public DataTable SelectRoles()
        {
            string consulta = @"SELECT id, nombre 
                                FROM Roles 
                                ORDER BY id";

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = consulta;

            return darDataSet(command).Tables[0];
        }

        public DataTable SelectPermisos()
        {
            string consulta = @"SELECT id,
                                    nombre  
                                FROM Permisos
                                ORDER BY id";

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = consulta;

            return darDataSet(command).Tables[0];
        }

        public DataTable SelectPermisosRoles()
        {
            string consulta = @"SELECT * 
                                FROM Roles R 
	                                LEFT OUTER JOIN PermisosRoles PR 
		                                ON R.id = PR.idRol
                                ORDER BY PR.idPermiso";

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = consulta;

            return darDataSet(command).Tables[0];
        }

        public DataTable SelectPermisosDeUsuario(int usuarioId)
        {
            string consulta = @"SELECT P.nombre, leer, editar
                    FROM Usuarios U
	                    INNER JOIN PermisosRoles PR ON U.idRol = PR.idRol
	                    INNER JOIN Permisos P ON PR.idPermiso = P.id
                    WHERE U.id = " + usuarioId;

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = consulta;

            return darDataSet(command).Tables[0];
        }

        public void ActualizarPermisoRol(int idPermiso, int idRol, bool valor, bool lectura)
        {
            using (SqlConnection objConexion = new SqlConnection(CCadenaConexionSql.cadConexion()))
            {
                if (objConexion.State == ConnectionState.Closed)
                    objConexion.Open();

                string consulta = @"INSERT INTO PermisosRoles (idPermiso, idRol, leer, editar)
                            SELECT " + idPermiso + @", " + idRol + @", 0, 0 
                            WHERE NOT EXISTS(SELECT * FROM PermisosRoles X WHERE X.idPermiso = " + idPermiso + @" AND X.idRol = " + idRol + @")";

                SqlCommand command = new SqlCommand();
                command.Connection = objConexion;
                command.CommandType = CommandType.Text;
                command.CommandText = consulta;
                command.ExecuteNonQuery();

                consulta = @"UPDATE PermisosRoles SET " + (lectura ? "leer" : "editar") + @" = " + (valor ? "1" : "0") + @"
                            WHERE idPermiso = " + idPermiso + @" AND idRol = " + idRol;

                command = new SqlCommand();
                command.Connection = objConexion;
                command.CommandType = CommandType.Text;
                command.CommandText = consulta;
                command.ExecuteNonQuery();

                objConexion.Close();
            }
        }
    }
}